Contents
Windows ISQL Help Contents

Overview of Windows ISQL.

Tasks you can perform with Windows ISQL.

Menu Commands provides help on the menu commands available in the Interactive SQL window.

SQL Language Reference provides a comprehensive reference for SQL statements that can be executed through Windows ISQL.


Windows ISQL Overview

Windows ISQL enables you to enter SQL commands interactively from a Windows 3.1 Client against InterBase databases. Windows ISQL runs on a client machine, but can attach to a local InterBase database running on the client machine or to remote databases running on any server on the network.

The Interactive SQL window contains pull-down menus, the SQL Statement area where you enter SQL statements, the ISQL Output area where ISQL displays output, and control buttons.

The menus are:

File menu: Contains commands to create and drop a database, connect to and disconnect from a database, extract data definition statements, save output results to a file, save a session to a file, commit and roll back work, and exit ISQL.

Edit menu: Contains commands to copy and paste commands to and from the Windows clipboard, and other related commands.

Session menu: Contains commands to set basic and advanced ISQL settings that apply to the current session, display the current settings, and the version of the current database.

View menu: Contains a command to view metadata in the current database, such as domains, tables, triggers, and so on.

Extract menu: Contains commands to extract metadata from the database, or a selected table or view.

Help menu: Provides online help.

To execute an SQL statement, position the mouse cursor in the SQL Statement area, type it in, and choose the Run button. ISQL will then display the output in the ISQL Output area.

The buttons in the ISQL window are:

Run: Executes the current SQL statement, displayed in the SQL statement area. Each time you successfully execute an SQL statement, it becomes part of the ISQL command history. The keyboard shortcut for this button is Alt+R.

Previous: Executes the previous SQL statement in the command history. The keyboard shortcut for this button is Alt+P.

Next: Executes the next SQL statement in the command history. The keyboard shortcut for this button is Alt+N.

Save to File: Saves the entire contents of the scrolling output area to file. The keyboard shortcut for this button is Alt+S.


Tasks

Connecting to a Database

Creating a Database

Dropping a Database

Executing SQL Statements

Running an ISQL Script

Saving Results to a File

Extracting Metadata

Changing Windows ISQL Settings

Viewing Metadata

Exiting Windows ISQL


Executing SQL Statements

In Windows ISQL, you can execute SQL statements:

Interactively, one statement at a time.

From an SQL script file.

To execute an SQL statement interactively, type the statement in the SQL Statement text field and click on the Run button or press Alt+R. The results are displayed in the ISQL Output area.

To execute an SQL Script File, choose File | Run an ISQL Script. A standard Open dialog box will appear. Enter the path and name of the file and click on OK. The results will then be displayed in the main window.


Extracting Metadata

Windows ISQL enables you to extract metadata for the entire database and for a specific table or view.

To extract data definition statements (metadata) from a database to a file, choose Extract | SQL Metadata for Database....

To extract metadata for a single table, choose Extract | SQL Metadata for Table....

To extract metadata for a single view, choose Extract | SQL Metadata for View....


Changing Windows ISQL Settings

The Session menu enables you to change ISQL settings for the current session.


Menu Commands

The Windows ISQL menus are:

File menu, that provides commands to create and drop a database and connect to and disconnect from a database, run an ISQL script, save results and the session to a file, commit and rollback work, and exit Windows ISQL.

Edit menu, that provides commands to copy and paste commands to and from the Windows clipboard, and other related commands.

Session menu, that enables you to customize the Windows ISQL environment for the current session, view the current settings and view the versions of Windows ISQL and the server to which it is currently connected.

View menu, that contains a command to view metadata in the current database.

Extract menu, that enables you to extract SQL metadata from the database or a selected table or view.


File Menu

The File menu contains the following commands:

Connect to Database

Create Database

Drop Database

Run an ISQL Script

Save Result to a File

Save Session to a File

Commit Work

Rollback Work

Exit


Edit Menu

The Edit menu contains the following commands:

Undo

Copy

Paste

Select All

Clear Output


Session Menu

The Session menu contains the following commands:

Basic Settings

Advanced Settings

Display Connect Version

Display Settings


View Menu

The View menu contains the following command:

Metadata Information


Extract Menu

The Extract menu contains the following commands:

SQL Metadata for Database

SQL Metadata for Table

SQL Metadata for View


Connect to Database

To use Windows ISQL, you must first connect to a database. Choose File | Connect to Database.... The Database Connection dialog box will then appear.


Database Connection Dialog Box

The Database Connection Dialog Box enables you to connect to a local or remote database server.

If an InterBase local database server exists on your machine, the Local button will be enabled.

To connect to a remote InterBase database server, Choose Remote and enter the name of the database server in the Server field, or click on the drop-down list and select a server from the list of previously used servers. In the Network Protocol field, click on TCP/IP, Novell SPX, or NetBEUI (Named Pipes).

In the Database text field, enter the name of the database to which to connect (including full directory path), or click on the drop-down list and select a database from the list of previously used databases.

A user name and password are required for connection to a database server. In the User Name and password text fields, enter a valid InterBase user name and password. The password will not be displayed.

To connect to a database, your user name and password must have been entered in the security database on the server. Use the Server Manager to add entries to the security database.

This dialog box stores user information in INTERBAS.INI in the WINDOWS directory. This file will keep a record of the last ten servers and databases connected to, and the user name and network protocol used for each server. It is also used by Server Manager.

If you have trouble connecting to a remote server, see the troubleshooting section in the Windows Client User Guide or READIB.TXT.


Create Database

To create a new database and connect to it, choose File | Create Database.... If currently connected to a database, a dialog box will prompt you to commit changes to it before creating and connecting to a new one. If you click on No, then all database changes since the last commit will be rolled back. If you click on Yes, then database changes will be committed.

Then the Database Creation dialog box will open.


Database Creation Dialog Box

This dialog box enables you to create a new database on a local or remote database server.

If an InterBase local database server exists on your machine, the Local button will be enabled.

To create a remote InterBase database, Choose Remote and enter the name of the server on which to create the database in the Server field. Select the communications protocol to use in the Network Protocol field. Click on the arrows to the right of these fields to display a drop-down list of servers previously connected to and communication protocols, respectively.

A user name and password are required to create a database. In the User Name and password text fields, enter a valid InterBase user name and password. The password will not be displayed.

In the Database field, enter the name of the database to create, including the full file name and directory path appropriate for the type of server on which you are creating the database.

In the Database Options area, enter any additional options of the CREATE DATABASE statement, such as PAGE_SIZE, DEFAULT CHARACTER SET, or secondary files. A complete list of CREATE DATABASE options is in the online SQL Reference. To create a basic database without any options, leave the Database Options area blank.

Click on OK to create the database. ISQL will then create the database on the specified server and connect to the database.


Drop Database

Dropping a database deletes the database to which ISQL is currently connected, removing both data and metadata. After choosing File | Drop Database, a dialog box will ask you to confirm that you want to delete the database. A database can be dropped only by its creator or the SYSDBA user.

Caution:	Dropping a database deletes all data and metadata in the database.


Save Results to a File

To save all the output results of the current session to a text file, choose File | Save Results to File.... The Save As dialog box will open enabling you to name the file.

Click on the desired directory and file name or type the file name to which to save output, and click on OK.

Only the ISQL output, not the statements entered, will be saved to the specified file.


Save Session to a File

To save the commands and ISQL output of the current session to a file, choose File | Save Session to a File. This is useful if you want to build an ISQL script file on the fly. The Save As dialog box will open enabling you to name the file.


Commit Work

Changes to the database from data definition (DDL) statements-for example, CREATE and ALTER statements-are automatically committed by default. To turn off automatic commit of DDL, choose Session | Basic ISQL Settings and click off the Auto Commit DDL check box.

Changes made to the database by data manipulation (DML) statements-for example INSERT and UPDATE-are not permanent until they are committed. Commit changes by choosing File | Commit Work.

To undo all database changes from DML statements since the last commit, choose File | Rollback Work.


Rollback Work

Changes to the database from data definition (DDL) statements-for example, CREATE and ALTER statements-are automatically committed by default. To turn off automatic commit of DDL, choose Session | Basic ISQL Settings and click off the Auto Commit DDL check box.

Changes made to the database by data manipulation (DML) statements-for example INSERT and UPDATE-are not permanent until they are committed. Commit changes by choosing File | Commit Work.

To undo all database changes from DML statements since the last commit, choose File | Rollback Work.


Save As Dialog Box

This standard dialog box enables you to name the file to which SQL results are saved. Enter the desired file name in the text field, or choose the directory and file in the dialog box and click on OK.


Exit

To exit Windows ISQL, choose File | Exit. A dialog box will confirm that you want to exit.

If connected to a database, this command will disconnect ISQL from it.


Undo

This command undoes the last Copy, Edit, or Paste command.


Copy

This command copies the currently selected text to the Windows clipboard. You can then choose Edit | Paste (or press Ctrl+V) to paste the text to the SQL Statement area. You can also paste copied text to another Windows application, such as Notepad.


Paste

This command pastes the contents of the Windows clipboard to the SQL statement area at the current cursor position. You can also paste text to another Windows application, such as Notepad.  The shortcut key for this command is Ctrl+V.

Copy text into the Windows clipboard with Edit | Copy or Ctrl+C.


Select All

This command selects all the text in the area where the cursor is. If the cursor is in the SQL Statement area, it will select the last statement entered. If the cursor is in the ISQL Output Area, it will select all of the output.

Once text is selected, you can then choose Edit | Copy or press Ctrl+C to copy the selected text to the Windows clipboard. You can then paste the text into other windows applications such as Notepad.


Clear Output

This command clears the ISQL Output area. The cursor needs to be in the ISQL Output area for this option to be available.


Basic Settings

Basic ISQL settings include:

Display of Query Plan

Auto Commit of DDL Statements

Display Rows in List Format or Table Format

Display of Row Count

Display Time Data Type for DATE columns

To modify basic ISQL settings, choose Session | Basic ISQL Settings.... The Basic ISQL Set Options dialog box will then appear.


Basic ISQL Set Options Dialog Box

This dialog box enables you to modify all the basic ISQL settings that can be toggled on or off. Each setting has a corresponding check box. If there is an "X" in the box, then the setting is ON. Otherwise it is OFF. Click on the check box or the setting name to toggle the setting. Basic settings are:

Display Query Plan: If ON, ISQL will display the query plan chosen by the optimizer . To modify the optimizer plan, use the PLAN option of the SQL SELECT statement.

Auto Commit DDL: If ON (default), data definition statements commit immediately. Recommended. If OFF, DDL statements must be manually committed or rolled back.

Display in List Format: If ON, ISQL displays data in vertical column format, one row at a time, with headings on left and column entries on right. If OFF, ISQL displays data in tabular format, which may wrap longer rows.

Display Row Count: If ON, ISQL will display the number of rows returned by each SELECT query.

Display Time Data Type: If ON, time as well as date will be displayed for columns of DATE data type. If OFF, only the date portion of DATE data types is displayed.


Advanced Settings

Advanced ISQL settings are:

BLOB Display

Character Set

To modify advanced ISQL settings, choose Session | Advanced ISQL Settings.... The Advanced ISQL Set Options dialog box will open.


Advanced ISQL Set Options Dialog Box

This dialog box enables you to modify the advanced ISQL settings that take parameters.

BLOB Display

Determines the display of a BLOB subtype. SELECT always displays the BLOB ID for columns of BLOB data type. By default, a SELECT will also display actual BLOB data of text subtypes beneath the associated row.

The choices are:

Disable BLOB Display: Do not display contents of BLOB columns.

Display ALL BLOBs: Display BLOB data of all subtypes.

Restrict BLOB Display: Display contents of BLOB columns only for the specified subtype. Use 0 for unknown subtype; 1 for text subtype (the default).

Character Set

This setting determines the active character set for strings for subsequent database connections. It enables you to override the default character set for a database. Click on the arrow to the right of the Character Set field to display a drop_down list of available character sets. Click on a character set from this list to select it.

Specify the character set before connecting to the database for which to use it.


Display Settings

Choose Session | Display Settings to display all the current basic and advanced ISQL settings (SET options). The settings will be displayed in the ISQL Output area. For example, here is the display of the default settings:

SET

Print statistics:    OFF

List format:         OFF

Row Count:           OFF

Autocommit DDL:      ON

Access Plan:         OFF

Display BLOB type:   1

Terminator:          ;

Time:                ON


Display Connect Version

Choose Session | Display Connect Version to display the version of the InterBase server to which ISQL is currently connected.


Metadata Information

Choose View | Metadata Information to display names and information on database metadata such as tables, domains, check constraints, system tables, triggers, and stored procedures. The View Information dialog box will open.


View Information Dialog Box

This dialog box enables you to select the type of metadata objects about which you want information. Select the type of metadata object by clicking on the down arrow to the right of the View Information On text field. This will display a drop-down list of types of metadata objects. Select an object type from the list by clicking on it. To display the names of all objects of that type, choose OK. To display information on a specific object of the selected type, enter the object name in the Object Name text field and then choose OK.

Check: Displays check constraints for the specified table. You must specify a table name.

Database: Displays the current database's file name, shadow files, page size and allocation, and sweep interval. You cannot enter an object name.

Domain: Displays:

Names of all domains in the database (with no object name).

Name and data type of the specified domain.

Exception: Displays:

Names of all exceptions in the database, their associated messages, and names of triggers and stored procedures which use them (with no object name).

Name, message, and names of triggers and stored procedures that use the specified exception.

Filter: Displays:

Names of all BLOB filters in the database (with no object name).

For a specific BLOB filter, displays input and output subtype, module (or library) name, and entry point.

Function: Displays:

Names of all user-defined functions in the database (with no object name).

All information about the specified function.

Generator: Displays:

Names and current values of all generators (with no object name).

Name and current value of the specified generator.

Grant: Displays permissions for the specified table or view.

Index: Displays:

Names of all indexes in the database, their constituent columns, and uniqueness (with no object name).

Names of all indexes for a specific table, their constituent columns, and uniqueness (if you specify a table name as the object name).

Constituent columns and uniqueness for a specific index (if you specify an index as the object name).

Procedure: Displays:

Names and dependencies of all stored procedures in the database (with no object name).

Procedure bodies, input parameters, and output parameters of the specified stored procedure.

Settings: Displays all the current ISQL settings options.

System: Displays the names of system tables and system views for the current database.

Table: Displays:

Names of all tables in the database (with no object name).

Columns, data types, PRIMARY KEY, FOREIGN KEY, and CHECK constraints for the specified table.

Trigger: Displays:

Names of all triggers in the database and the tables for which they are defined (with no object name).

Trigger bodies for triggers defined for the specified table.

Text of the body of the specified trigger.

View: Displays:

Names of all views in the database (with no object name).

Columns, data types, and view source for the specified view.


Run an ISQL Script

To execute a file containing ISQL statements, choose File | Run an ISQL Script. The standard Open dialog box will appear, enabling you to select an ISQL script file to run. ISQL will prompt you to commit or rollback changes to the database to which ISQL is currently connected.

Creating an ISQL Script File

You can use any text editor to create an SQL script file, as long as the final file format is "plain text" (ASCII).

Every ISQL script file must begin with either a CREATE DATABASE statement or a CONNECT statement (including user name and password)

To specify a local database on which the script file operates. For example:

CONNECT "C:\IBLOCAL\EXAMPLES\MYDB.GDB"

USER "myusername" PASSWORD "mypassword";

To specify the remote database on which the script file operates. For example:

CONNECT "SVR1:VOL2:\USERS\EXAMPLES\MYDB.GDB"

USER "myusername" PASSWORD "mypassword";

To connect using TCP/IP, separate the server and database names with a colon, as in the above example. To connect using NetWare SPX/IPX, separate the server and database names with "@", for example:

CONNECT "SVR1@VOL2:\USERS\EXAMPLES\MYDB.GDB"

USER "myusername" PASSWORD "mypassword";

To connect to a Windows NT server using NetBEUI/Named Pipes, precede the server name with either a double backslash (\\) or a double slash (//), and separate disk, directory, and file names with slashes or backslashes. For example:

CONNECT "\\SVR3\D:\USERS\EXAMPLES\MYDB.GDB

"USER "myusername" PASSWORD "mypassword";

For the full syntax of CONNECT and CREATE DATABASE, see the online SQL Reference.

An ISQL script may contain any SQL statements, as described in the Language Reference plus ISQL SET commands. Each script file must end with either EXIT to commit database changes or QUIT to roll back changes made by the script.

Each SQL statement must be terminated by a semicolon (;) or the current terminator if it has been changed with SET TERM.

Adding Comments

ISQL scripts are commented exactly like C programs:

/* comment */

A comment may occur on the same line as ISQL commands and may be of any length, as long as it is preceded by "/*" and followed by "*/".

ISQL SET Statements

SET Statements are used to configure the ISQL environment from a script file. When using ISQL interactively, perform these same functions with the Session menu items. You cannot enter SET statements interactively in the SQL Statement area.

The SET statements are listed in the following table:

Statement	Description

SET AUTODDL	Toggles the commit feature for DDL statements.

SET BLOBDISPLAY n	Turns on the display of BLOB type n. The parameter n is required to display BLOB types.

SET COUNT	Toggles the count of selected rows on or off.

SET ECHO	Toggles the display of each command on or off.

SET LIST string	Displays columns vertically or horizontally.

SET NAMES	Specifies the active character set.

SET PLAN	Determines whether or not to display the optimizer's query plan.

SET STATS	Toggles the display of the performance statistics on or off.

SET TERM string	Allows you to change the terminator character from the default semicolon (;) to any string.

SET TIME	Toggles display of time in DATE values.

By default all settings are initially OFF except AUTODDL, and the terminator is a semicolon (;). Each time you start an ISQL session, it begins with the default settings.

After an ISQL script completes, the settings return to their values before the script was run. So you can modify the settings for interactive use, then change them as needed in an ISQL script, and after running the script they automatically return to their previous configuration.


SQL Metadata for Database

To extract data definition statements (metadata) from a database to a file, choose Extract | SQL Metadata for Database.... A dialog box inquires if you want to save the metadata to a file. If you click on Yes, then another dialog box will open, enabling you to enter the name of the file to which to extract the metadata. If you click on No, then the metadata will be displayed to the Output area only. If you click on Cancel, then the operation will be canceled.


SQL Metadata for Table

To extract metadata for a single table, choose Extract | SQL Metadata for Table.... A dialog box opens that enables you to extract metadata for a specified table. Click on the arrow to the right of the Table Name field to see a drop-down list of tables in the database. Choose a table, then click on OK to extract metadata from that table.

Another dialog box will open, asking whether to save output to a file. Click on Yes to save the metadata to a text file, No to display the metadata to the Output area only, or Cancel to cancel the operation. If you choose to save the results to a file, another dialog box will open, enabling you to enter the file name.


Extract SQL Metadata for View
SQL Metadata for View

To extract metadata for a view, choose Extract | SQL Metadata for View.... The Extract View Metadata box will open.

This dialog box enables you to extract metadata for a specified view. Click on the arrow to the right of the View Name field to see a drop-down list of views in the database. Choose a view, then click on OK to extract metadata from that view.

Another dialog box will open, asking whether to save output to a file. Click on Yes to save the metadata to a text file, No to display the metadata to the Output area only, or Cancel to cancel the operation. If you choose to save the results to a file, another dialog box will open, enabling you to enter the file name.